/*
 * This file Copyright (c) 2015. Walle.
 * (http://www.wallellen.com). All rights reserved.
 *
 *
 * This file is dual-licensed under both the
 * Walle Agreement (WA) and the GNU General Public License.
 * You may elect to use one or the other of these licenses.
 *
 * This file is distributed in the hope that it will be
 * useful, but AS-IS and WITHOUT ANY WARRANTY; without even the
 * implied warranty of MERCHANTABILITY or FITNESS FOR A
 * PARTICULAR PURPOSE, TITLE, or NONINFRINGEMENT.
 * Redistribution, except as permitted by whichever of the GPL
 * or WA you select, is prohibited.
 *
 * 1. For the GPL license (GPL), you can redistribute and/or
 * modify this file under the terms of the GNU General
 * Public License, Version 3, as published by the Free Software
 * Foundation.  You should have received a copy of the GNU
 * General Public License, Version 3 along with this program;
 * if not, write to the Free Software Foundation, Inc., 51
 * Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
 *
 * 2. For the Walle Agreement (WA), this file
 * and the accompanying materials are made available under the
 * terms of the WA which accompanies this distribution, and
 * is available at http://www.wallellen.com/agreement.html
 *
 *
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER
 */

package com.wallellen.core.sql.query;

import com.wallellen.core.sql.result.DataResult;
import com.wallellen.core.sql.result.DataResults;
import org.hibernate.Session;

import java.sql.SQLException;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * Created by walle on 12/9/15.
 */
public class HibernateQueryHandle {
    private static final String SqlSelectSize = " select count(*) num ";

    protected static DataResult runSqlQuery(Session session, String querysql, String countsql, int pagesize, int currentPage)
            throws SQLException {
        long startTime = new Date().getTime();
        if (querysql.indexOf("*") > 0) {
            throw new SQLException("*不能存在于查询语句中，请明确查询字段!");
        }
        querysql = querysql.toUpperCase();
        DataResult result = null;

        int sizeResourt = pagesize;
        int firstResourt = (currentPage - 1) * sizeResourt;
        String titles = querysql.substring(0, querysql.indexOf("FROM"));
        List limap = DataResults.getMaps(titles, runLimitQuery(session, querysql, firstResourt, sizeResourt));

        result = DataResult.getInstance(limap, countsql == null ? limap.size() : runLimitQueryNum(session, countsql), currentPage, pagesize);

        long endTime = new Date().getTime();
        result.setRuntime(endTime - startTime);
        return result;
    }

    protected static DataResult runDataQuery(Session session, DataQuery dataquery)
            throws SQLException {
        long startTime = new Date().getTime();
        DataResult result = null;
        try {
            String upsql = praseSQL(dataquery);
            String partSql = upsql.substring(upsql.indexOf(" FROM "));
            String headsql = upsql.substring(0, upsql.indexOf(" FROM "));
            if (headsql.indexOf("*") >= 0) {
                throw new SQLException("select can't contain *");
            }
            int sizeResourt = dataquery.getPagesize();
            int firstResourt = (Integer.valueOf(dataquery.getCurrentPage().trim()).intValue() - 1) * sizeResourt;

            List limap = DataResults.getMaps(dataquery.getTitles(), runLimitQuery(session, upsql, firstResourt, sizeResourt));

            if (dataquery.isDistinct()) {
                if (upsql.indexOf("ORDER BY") > 0) {
                    upsql = upsql.substring(0, upsql.indexOf("ORDER"));
                }
                partSql = " FROM (" + upsql + ") counum";
            }

            int count = limap.size();
            if (dataquery.isCount()) {
                if (partSql.toUpperCase().indexOf("ORDER BY") > 0) {
                    partSql = partSql.substring(0, partSql.toUpperCase().indexOf("ORDER BY"));
                }

                partSql = " select count(*) num " + partSql;
                count = runLimitQueryNum(session, dataquery.getCountSql() == null ? partSql : dataquery.getCountSql());
            }

            result = DataResult.getInstance(limap, count, Integer.valueOf(dataquery.getCurrentPage()).intValue(), dataquery.getPagesize());
        } catch (Exception e) {
            throw new SQLException(e);
        }
        long endTime = new Date().getTime();
        result.setRuntime(endTime - startTime);
        return result;
    }

    public static String praseSQL(DataQuery dataquery)
            throws SQLException {
        String distinct = "";
        if (dataquery.isDistinct()) {
            distinct = " distinct ";
        }
        StringBuffer SQL_run = new StringBuffer().append("select ").append(distinct).append(dataquery.getTitles().toUpperCase()).append(getSql_part(dataquery));

        return upCaseSQLKEY(SQL_run.toString());
    }

    private static String upCaseSQLKEY(String SQL) {
        SQL = SQL.replace(" select ", " SELECT ");
        SQL = SQL.replace(" from ", " FROM ");
        SQL = SQL.replace(" as ", " AS ");
        SQL = SQL.replace(" where ", " WHERE ");
        SQL = SQL.replace(" order by ", " ORDER BY ");
        return SQL;
    }

    private static List<Object[]> runLimitQuery(Session session_, String Sql, int firstResourt, int sizeResourt) {
        try {
            org.hibernate.SQLQuery sqlQuery = session_.createSQLQuery(Sql);
            sqlQuery.setFirstResult(firstResourt);
            sqlQuery.setMaxResults(sizeResourt);
            return sqlQuery.list();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static int runLimitQueryNum(Session session_, String countSql) {
        org.hibernate.SQLQuery sqlQuery = session_.createSQLQuery(countSql);
        Object num = sqlQuery.list().get(0);
        Integer renum = Integer.valueOf(num.toString());
        int n = renum.intValue();
        return n;
    }

    private static String getSql_part(DataQuery query) throws SQLException {
        String sql_part = getSql_from(query) + getSql_where(query) + getSortWhere(query);

        return sql_part;
    }

    private static String getSortWhere(DataQuery query) {
        List sortList = query.sort;
        StringBuffer where = new StringBuffer(" order by ");
        boolean isHaveSort = false;
        Iterator iterator = sortList.iterator();
        while (iterator.hasNext()) {
            DBSort name = (DBSort) iterator.next();
            if ((name != null) && (name.getSortTitleText() != null) && (name.getSortTypeText() != null) && (!name.getSortTitleText().equals("")) && (!name.getSortTypeText().equals(""))) {
                where.append(name.getSortTitleText());
                where.append(" ");
                where.append(name.getSortTypeText());
                isHaveSort = true;
                if (iterator.hasNext()) {
                    where.append(" , ");
                }
            }
        }
        if (!isHaveSort) {
            return "";
        }
        return where.toString();
    }

    private static String getSql_from(DataQuery query) {
        String sql_from = " from " + query.getTables() + " ";
        return sql_from;
    }

    private static String getSql_where(DataQuery query) throws SQLException {
        if (query.queryRule == null) {
            throw new SQLException("whereList is null!");
        }
        Iterator it_ = query.queryRule.iterator();
        StringBuffer where_ = new StringBuffer("");
        where_.append(" where 1=1 ");
        while (it_.hasNext()) {
            DBRule _queryld = (DBRule) it_.next();
            if ((_queryld != null) && (_queryld.getValue() != null))
                where_.append(_queryld.getThisLimit());
        }
        String sql_where = where_.toString();
        if ((query.getUserWhere() != null) && (query.getUserWhere().trim().length() > 1)) {
            sql_where = sql_where + " " + query.getUserWhere() + " ";
        }
        return sql_where;
    }
}
